/*
 * 软件版权: 恒生电子股份有限公司
 * 修改记录:
 * 修改日期     修改人员  修改说明
 * ========    =======  ============================================
 * 2021/3/30  lisy31662  新增
 * ========    =======  ============================================
 */
package com.sansi.pencilbook.mapper;

import com.sansi.pencilbook.entity.UserBookRelation;
import org.apache.ibatis.annotations.*;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能说明: 用户和书籍的关系基础增删改查
 *
 * @author lisy31662
 */
@Mapper
public interface UserBookRelationMapper {

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation")
  public List<UserBookRelation> findAll() throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where user_id=#{userId}")
  public List<UserBookRelation> findByUser(int userId) throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where relation=#{relation}")
  public List<UserBookRelation> findByRelation(String relation) throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where relation=#{relation} and user_id=#{userId}")
  public List<UserBookRelation> findByUserRelation(int userId, String relation) throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where book_isbn=#{bookIsbn} and user_id=#{userId}")
  public List<UserBookRelation> findByUserBook(int userId, String bookIsbn) throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where book_isbn=#{bookIsbn} and relation=#{relation} and user_id=#{userId}")
  public List<UserBookRelation> findByUserBookRelation(int userId, String bookIsbn, String relation) throws SQLException;

  @Select("select user_id, store_id, book_isbn, relation, num from user_book_relation where book_isbn=#{bookIsbn}")
  public List<UserBookRelation> findByBook(String bookISBN) throws SQLException;

  @Insert("insert into user_book_relation (user_id, store_id, book_isbn, relation, num) values(#{userId},#{storeId},#{bookIsbn},#{relation},#{num})")
  public int saveUserBook(UserBookRelation userBookRelation) throws SQLException;

  @Update("update user_book_relation set num=#{num}" +
      "where user_id=#{userId} and store_id=#{storeId} and book_isbn=#{bookIsbn} and relation=#{relation}")
  public int updateUserBook(UserBookRelation userBookRelation) throws SQLException;

  @Delete("delete from user_book_relation where user_id=#{userId} and store_id=#{storeId} and book_isbn=#{bookIsbn} and relation=#{relation}")
  public int deleteUserBook(UserBookRelation userBookRelation) throws SQLException;
  
}